/**
 * 
 */
package infocluster.task;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Hashtable;

import jxl.*;
import jxl.demo.CSV;
import jxl.format.Border;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import infocluster.prototype.ExcelProcessTask;
import infocluster.prototype.IExcelProcessTask;
import infocluster.prototype.Merchant;

/**
 * @author 08302010017 Xi Qi
 *
 */
@SuppressWarnings("deprecation")
public class CompareExcelProcessTask extends ExcelProcessTask implements
		IExcelProcessTask {

	private static final String TEMP_NEW_MERCHANT_LIST = "/new_merchant_list.xls";
	private static final String TEMP_NEW_OUTLET_LIST = "/new_outlet_list.xls";
	private static final String TEMP_NEW_OFFER_LIST = "/new_offer_list.xls";
	
	public static final WritableFont NORMAL_FONT = new  WritableFont(WritableFont.TIMES, 11 ,WritableFont.NO_BOLD);
	public static final WritableFont BOLD_FONT = new  WritableFont(WritableFont.TIMES, 11 ,WritableFont.BOLD);
	
	private static final String[] MERCHANT_COLUMN = {"name","name_zh","category","partner","contact_name","contact_phone_number","contact_email","website_url","description","description_zh"};
	private static final String[] OUTLET_COLUMN = {"name","name_zh","merchant","latitude","longitude","address","address_zh","district","phone_number"};
	private static final int[] OUTLET_COLUMN_NUM = {10,11,0,-1,-1,13,14,15,16};
	private static final String[] OFFER_COLUMN = {"name","name_zh","highlight","highlight_zh","merchant","description","description_zh","expiration_date","one_off","scb_only"};
	private static final int[] OFFER_COLUMN_NUM = {17,18,19,20,0,21,22,-1,-1,-1};
	private static final String[] CHANGE_COLUMN = {"name","name_zh","null","null"};
	private static final int[] CHANGE_COLUMN_NUM = {0,1,-1,-1};

	
	/* (non-Javadoc)
	 * @see infocluster.prototype.IExcelProcessTask#execute()
	 */
	public boolean execute() throws IOException {
		
		WorkbookSettings setting = new WorkbookSettings(); 
		setting.setEncoding("iso-8859-1");   
		
		Hashtable<String,String> merchants = new Hashtable<String, String>();
		Hashtable<String,String> newMerchants = new Hashtable<String, String>();
		Hashtable<String,String> offers = new Hashtable<String, String>();
		
		try {
			Workbook offerWb = Workbook.getWorkbook(this.getSource(0), setting);
			Workbook merchantWb = Workbook.getWorkbook(this.getSource(1), setting);
			WritableWorkbook newOfferWb = Workbook.createWorkbook(this.getDest(0));
			WritableWorkbook newMerchantWb = Workbook.createWorkbook(this.getDest(1));
			//File uri = ResourceLocator.get().getFile(TEMP_NEW_MERCHANT_LIST);
			String path = this.getDest(0).getParent();
			WritableWorkbook newlyMerchantWb = Workbook.createWorkbook(new File(path+TEMP_NEW_MERCHANT_LIST));
			WritableWorkbook newlyOutletWb = Workbook.createWorkbook(new File(path+TEMP_NEW_OUTLET_LIST));
			WritableWorkbook newlyOfferWb = Workbook.createWorkbook(new File(path+TEMP_NEW_OFFER_LIST));
			
			Sheet merchantSheet = merchantWb.getSheet(0);
			int mRowCount = merchantSheet.getRows();
			int mColumnCount = merchantSheet.getColumns();
			
			WritableSheet newMerchantSheet = newMerchantWb.createSheet("Merchant", 0);
			
			// build existing merchant list
			for(int j=0;j<mColumnCount;j++){
				Cell c = merchantSheet.getCell(j,0);
				WritableCellFormat cf = new WritableCellFormat(c.getCellFormat()); 
				String content = c.getContents();
				
				Label label = null;
				newMerchantSheet.setColumnView(j, merchantSheet.getColumnView(j));
				label = new Label(j, 0, content);
				label.setCellFormat(cf);
				newMerchantSheet.addCell(label); 
			}
			
			for(int i=1;i<mRowCount;i++){
				String eName = merchantSheet.getCell(Merchant.ENAME, i).getContents().trim().replace("\n", "");
				String cName = merchantSheet.getCell(Merchant.CNAME, i).getContents().trim().replace("\n", "");
				if(!eName.equals("") && !cName.equals("") && merchants.get(eName) == null){
					merchants.put(eName, cName);
					//copy to the new merchant list
					for(int j=0;j<mColumnCount;j++){
						Cell c = merchantSheet.getCell(j,i);
						WritableCellFormat cf = new WritableCellFormat(c.getCellFormat()); 
						String content = c.getContents().trim();						
						Label label = new Label(j, i, content);
						label.setCellFormat(cf);
						newMerchantSheet.addCell(label); 
					}
				}
			}
			
			Cell cell = merchantSheet.getCell(mColumnCount-1,0);
			WritableCellFormat format = new WritableCellFormat(cell.getCellFormat()); 
			newMerchantSheet.setColumnView(mColumnCount, merchantSheet.getColumnView(mColumnCount-1));
			String con = getNextMonth(cell.getContents().trim());						
			Label l = new Label(mColumnCount, 0, con);
			l.setCellFormat(format);
			newMerchantSheet.addCell(l); 
			
			Sheet offerSheet = offerWb.getSheet(0);
			int rowCount = offerSheet.getRows();
			int columnCount = offerSheet.getColumns();
			
			WritableSheet newOfferSheet = newOfferWb.createSheet("public-allRenew", 0);
			WritableSheet nMerchantSheet = newOfferWb.createSheet("Merchant",1);
			WritableSheet nOutletSheet = newOfferWb.createSheet("Outlet",2);
			WritableSheet nOfferSheet = newOfferWb.createSheet("Offer",3);
			WritableSheet nMerchantSheet_a = newlyMerchantWb.createSheet("Merchant",0);
			WritableSheet nOutletSheet_a = newlyOutletWb.createSheet("Outlet",0);
			WritableSheet nOfferSheet_a = newlyOfferWb.createSheet("Offer",0);
			
			// print the column headers
			for(int i=0;i<2;i++){
				for(int j=0;j<columnCount;j++){
					Cell c = offerSheet.getCell(j,i);
					WritableCellFormat cf = new WritableCellFormat(c.getCellFormat()); 
					cf.setFont(BOLD_FONT);
					String content = c.getContents();
					
					Label label = null;
					newOfferSheet.setColumnView(j, offerSheet.getColumnView(j));
					label = new Label(j, i, content);
					label.setCellFormat(cf);
					newOfferSheet.addCell(label); 
				}
			}
			
			for(int i=0;i<10;i++){
				String content = MERCHANT_COLUMN[i];				
				Label label = new Label(i, 0, content);				
				nMerchantSheet.addCell(label); 
				Label label1 = new Label(i, 0, content);
				nMerchantSheet_a.addCell(label1); 
			}
			
			for(int i=0;i<9;i++){
				String content = OUTLET_COLUMN[i];				
				Label label = new Label(i, 0, content);
				Label label1 = new Label(i, 0, content);
				nOutletSheet.addCell(label);
				nOutletSheet_a.addCell(label1); 
			}
			
			for(int i=0;i<10;i++){
				String content = OFFER_COLUMN[i];				
				Label label = new Label(i, 0, content);
				Label label1 = new Label(i, 0, content);
				nOfferSheet.addCell(label);
				nOfferSheet_a.addCell(label1);
			}
			
			int newMerchantCount = 1;
			int newOutletCount = 1;
			int newOfferCount = 1;
			int newlyMerchantCount = 0;
			for(int i=2;i<rowCount;i++){
				String eName = offerSheet.getCell(Merchant.ENAME,i).getContents().trim();
				String cName = offerSheet.getCell(Merchant.CNAME,i).getContents().trim();
				String offerName = offerSheet.getCell(17,i).getContents().trim();
				String cOName = merchants.get(eName);
				String hi = offerSheet.getCell(19,i).getContents().trim();
				for(int j=0;j<columnCount;j++){					
					Cell c = offerSheet.getCell(j,i);
					WritableCellFormat cf = new WritableCellFormat(c.getCellFormat());					
					cf.setFont(NORMAL_FONT);
					String content = c.getContents();
					content = content.trim();
					
					Label label = null;
					label = new Label(j, i, content);
					label.setCellFormat(cf);
					newOfferSheet.addCell(label); 
				}
				
				if(offers.get(eName+offerName+hi) == null){
					offers.put(eName+offerName+hi, offerName);
					for(int j=0;j<10;j++){
						int column = OFFER_COLUMN_NUM[j];
						if(column != -1){
							Cell c = offerSheet.getCell(column,i);
							String content = c.getContents().trim();		
							Label label = new Label(j, newOfferCount, content);
							Label label1 = new Label(j, newOfferCount, content);
							nOfferSheet.addCell(label); 
							nOfferSheet_a.addCell(label1); 
						}
					}
					newOfferCount++;
				}
				
				String cNName = newMerchants.get(eName);
				if(cOName == null){
					if(cNName == null){
						//System.out.println("New Merchant:"+cNName);
						for(int j=0;j<10;j++){
							Cell c = offerSheet.getCell(j,i);
							String content = c.getContents();
							content = content.trim();
							
							Label label = new Label(j, newMerchantCount, content);
							Label label1 = new Label(j, newMerchantCount, content);
							nMerchantSheet.addCell(label); 
							nMerchantSheet_a.addCell(label1); 
						}
						newMerchantCount++;
						
						for(int j=0;j<mColumnCount;j++){
							WritableCellFormat cf = new WritableCellFormat();
							cf.setBorder(Border.ALL, BorderLineStyle.THIN);
							String content = null;
							
							WritableFont font = new WritableFont(WritableFont.TIMES,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
							if(j==0){
								content = eName;
							}else if(j==1){
								content = cName;
							}else if(j==2){
								content = "New";
							}else{
								content = "hidden";
								font = new WritableFont(WritableFont.TIMES,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.RED);
							}
							cf.setFont(font);
							
							Label label = new Label(j, mRowCount+newlyMerchantCount , content);
							label.setCellFormat(cf);
							newMerchantSheet.addCell(label);							
						}
						WritableCellFormat cf = new WritableCellFormat();
						cf.setBorder(Border.ALL, BorderLineStyle.THIN);
						String content = null;
						
						WritableFont font = new WritableFont(WritableFont.TIMES,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
						content = "visible";
						font = new WritableFont(WritableFont.TIMES,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.GREEN);
						cf.setFont(font);
						
						Label label = new Label(mColumnCount, mRowCount+newlyMerchantCount , content);
						label.setCellFormat(cf);
						newMerchantSheet.addCell(label);
						
						newlyMerchantCount++;
					}
					
					for(int j=0;j<9;j++){
						int column = OUTLET_COLUMN_NUM[j];
						if(column != -1){
							Cell c = offerSheet.getCell(column,i);
							String content = c.getContents();
							content = content.trim();							
							Label label = new Label(j, newOutletCount, content);
							Label label1 = new Label(j, newOutletCount, content);
							nOutletSheet.addCell(label); 
							nOutletSheet_a.addCell(label1); 
						}
					}
					newOutletCount++;
				}
				
				newMerchants.put(eName, cName);
			}
			
			WritableCellFormat cf = new WritableCellFormat();
			WritableFont font;
			for(int i=1;i<mRowCount;i++){				
				font = new WritableFont(WritableFont.TIMES,12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
				Cell c = merchantSheet.getCell(0,i);
				String content = c.getContents().trim();
				Label label = null;
				if(newMerchants.get(content) == null){
					//set the merchant's state to hidden
					font.setColour(Colour.RED);
					label = new Label(mColumnCount, i, "hidden");					
				}else{
					//set the merchant's state to visible
					font.setColour(Colour.GREEN);
					label = new Label(mColumnCount, i, "visible");
				}
				cf = new WritableCellFormat(font);
				cf.setBorder(Border.ALL, BorderLineStyle.THIN);
				label.setCellFormat(cf);
				newMerchantSheet.addCell(label); 
			}
			
			newOfferWb.write(); 
			newOfferWb.close();
			newMerchantWb.write();
			newMerchantWb.close();
			newlyMerchantWb.write();
			newlyMerchantWb.close();
			newlyOutletWb.write();
			newlyOutletWb.close();
			newlyOfferWb.write();
			newlyOfferWb.close();
			
			offerWb.close();
			merchantWb.close();
			
			merchantWb = Workbook.getWorkbook(this.getDest(1));
			merchantSheet = merchantWb.getSheet(0);
			
			mRowCount = merchantSheet.getRows();
			mColumnCount = merchantSheet.getColumns();
			
			WritableWorkbook changeWb = Workbook.createWorkbook(this.getDest(5));
			WritableSheet changeSheet = changeWb.createSheet("Change",0);
			
			CHANGE_COLUMN_NUM[2] = mColumnCount-2;
			CHANGE_COLUMN_NUM[3] = mColumnCount-1;
			CHANGE_COLUMN[2] = merchantSheet.getCell(mColumnCount-2, 0).getContents().trim();
			CHANGE_COLUMN[3] = merchantSheet.getCell(mColumnCount-1, 0).getContents().trim();
			for(int i=0;i<4;i++){
				String content = CHANGE_COLUMN[i];				
				Label label = new Label(i, 0, content);
				changeSheet.addCell(label);
			}
			
			int changeCount = 0;
			for(int i=0;i<mRowCount;i++){
				String lastMonth = merchantSheet.getCell(CHANGE_COLUMN_NUM[2],i).getContents().trim();
				String thisMonth = merchantSheet.getCell(CHANGE_COLUMN_NUM[3],i).getContents().trim();
				if(!thisMonth.equals(lastMonth)){
					for(int j=0;j<4;j++){
						int column = CHANGE_COLUMN_NUM[j];
						if(column != -1){
							Cell c = merchantSheet.getCell(column,i);
							String content = c.getContents().trim();
							Label label = new Label(j, changeCount, content);
							changeSheet.addCell(label); 
						}
					}
					changeCount++;
				}
			}
			changeWb.write();
			changeWb.close();
			
			File f = null;
			
			Workbook newMerchantWb1 = Workbook.getWorkbook(new File(path+TEMP_NEW_MERCHANT_LIST),setting);
			new CSV(newMerchantWb1,new FileOutputStream(this.getDest(2)), "iso-8859-1", false);
			f = new File(path+TEMP_NEW_MERCHANT_LIST);
			f.delete();
			
			Workbook newOutletWb1 = Workbook.getWorkbook(new File(path+TEMP_NEW_OUTLET_LIST),setting);
			new CSV(newOutletWb1,new FileOutputStream(this.getDest(3)), "iso-8859-1", false);
			f = new File(path+TEMP_NEW_OUTLET_LIST);
			f.delete();
			
			Workbook newOfferWb1 = Workbook.getWorkbook(new File(path+TEMP_NEW_OFFER_LIST),setting);
			new CSV(newOfferWb1,new FileOutputStream(this.getDest(4)), "iso-8859-1", false);
			f = new File(path+TEMP_NEW_OFFER_LIST);
			f.delete();
			
			newMerchantWb1.close();
			newOutletWb1.close();
			newOfferWb1.close();
			
			return true;
			
		} catch (ArrayIndexOutOfBoundsException e) {
			e.printStackTrace();
			return false;
		} catch (BiffException e) {
			e.printStackTrace();
			return false;
		} catch (RowsExceededException e) {
			e.printStackTrace();
			return false;
		} catch (WriteException e) {
			e.printStackTrace();
			return false;
		}
	}


	/**
	 * @param date the string of month
	 * @return the string of next month
	 */
	private String getNextMonth(String date) {
		
		String temp[] = date.split("/");
		int year = Integer.parseInt(temp[0]);
		int month = Integer.parseInt(temp[1]);
		year += (month+1) / 12;
		month = (month+1) % 12;
		
		String newDate = year+"/";
		if(month > 9){
			newDate += month;
		}else{
			newDate += "0"+month;
		}
		
		return newDate;
	}
	
	@SuppressWarnings("unused")
	private String createBackupName(String name){
		String temp[] = name.split("\\.");
		return temp[0]+"-backup."+temp[1];
	}

}
